﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Maticsoft.DBUtility
{
    public class reportDbHelp
    {
        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataReader sdr = null;
        /// <summary>
        /// 对数据库的帮助类进行初始化，定义数据库的连接对象
        /// </summary>
        public reportDbHelp()
        {
            string connStr = ConfigurationManager.ConnectionStrings["ReportManagementConnectionString"].ConnectionString;
            conn = new SqlConnection(connStr);
        }
        private SqlConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }
        /// <summary>
        /// 执行不带参数的sql语句或者是存储过程并返回数据源DataTable
        /// </summary>
        /// <param name="cmdtext">sql语句或者是存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns>返回数据源DataTable</returns>
        public DataTable ExecuteQuery(string cmdText, CommandType ct)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.CommandType = ct;
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
        /// <summary>
        /// 执行带参数的sql语句或者是存储过程并返回数据源DataTable
        /// </summary>
        /// <param name="cmdtext">sql语句或者是存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns>返回数据源DataTable</returns>
        public DataTable ExecuteQuery(string cmdtext, SqlParameter[] paras, CommandType ct)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand(cmdtext, GetConn());
            cmd.CommandType = ct;
            cmd.Parameters.AddRange(paras);
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
        /// <summary>
        /// 执行不带参数的sql语句或者是存储过程
        /// </summary>
        /// <param name="cmdtext">sql语或者是存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns>返回的影响行数</returns>
        public int ExecuteNotQuery(string cmdtext, CommandType ct)
        {
            int res = 0;
            try
            {
                SqlCommand cmd = new SqlCommand(cmdtext, GetConn());
                cmd.CommandType = ct;
                res = cmd.ExecuteNonQuery();
            }
            catch(Exception e)
            {
                throw e;
            }
            finally
            {
                if (GetConn().State == ConnectionState.Open) {
                    conn.Close();
                }
            }
            return res;
        }
        /// <summary>
        /// 执行带参数的sql语句或者是存储过程
        /// </summary>
        /// <param name="cmdtext">sql语或者是存储过程</param>
        /// <param name="paras">参数</param>
        /// <param name="ct">命令类型</param>
        /// <returns>返回影响的行数</returns>
        public int ExecuteNotQuery(string cmdtext,SqlParameter[] paras,CommandType ct)
        {
            int res = 0;
            using (cmd = new SqlCommand(cmdtext,GetConn()))
            {
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);
                //cmd.ExecuteNonQuery();
                res = cmd.ExecuteNonQuery();
            }          
            return res;
        }
        public bool selectAdminNameIsCunZai(string adminName)
        {
            bool flag = false;
            string strsql = "select * from Admin where A_Id = '"+adminName+"' ";
            SqlCommand cmdd = new SqlCommand(strsql, GetConn());
            DataSet dss = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(strsql, GetConn());
            da.Fill(dss, "mytable");

            string result = dss.Tables[0].Rows[1].ItemArray[1].ToString().Trim();
            if (result == adminName)
            {
                flag = true;
            }          
            return flag;
        }
    }
}
